package com.qh.resourcehub.info.virtualresource.dao.impl;

import com.qh.resourcehub.info.entity.ApplicationData;
import com.qh.resourcehub.info.virtualresource.dao.VmApplicationDao;
import com.qh.resourcehub.utils.XuguUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Repository("VmApplicationDao")
public class VmApplicationDaoImpl implements VmApplicationDao {
    @Autowired
    private XuguUtils xuguUtils;

    String[] Ipcolumns = {"ID", "APPLICANT_UNIT"};

    @Override
    public List<Map<String, Object>> queryVmDataService(ApplicationData applicationData) {
        List<Map<String, Object>> result = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        PreparedStatement pstmtEmp = null;

        try {
            connection = xuguUtils.getConnection();
            List<Object> queryParams = new ArrayList<>();
            //查询
            StringBuilder sqlQuery = new StringBuilder("SELECT * FROM APPLICATION_DATA WHERE 1=1  ");
            // 动态条件拼接
            if (!StringUtils.isEmpty(applicationData.getApplyUser())){
                sqlQuery.append(" AND APPLY_USER = ?");
                queryParams.add(applicationData.getApplyUser());
            }
            // 分页参数
            int page = String.valueOf(applicationData.getPage()) != null ? applicationData.getPage() : 0;
            int size = String.valueOf(applicationData.getSize())!= null ? applicationData.getSize() : 10;
            int offset = page * size;
            sqlQuery.append(" LIMIT ? OFFSET ?");
            queryParams.add(size);
            queryParams.add(offset);

            // 创建 PreparedStatement
            pstmtUser = connection.prepareStatement(sqlQuery.toString());

            // 设置查询参数
            for (int i = 0; i < queryParams.size(); i++) {
                pstmtUser.setObject(i + 1, queryParams.get(i));
            }

            // 执行查询
            ResultSet rs = pstmtUser.executeQuery();
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (String column : Ipcolumns) {
                    row.put(column, rs.getObject(column));
                }
                result.add(row);
            }
            return result; // 表示成功
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmtUser != null) {
                    pstmtUser.close();
                }
                if (pstmtEmp != null) {
                    pstmtEmp.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return Collections.emptyList();
    }

    /**
     * IP新增
     *
     * @param applicationData
     * @return
     */
    @Override
    public int ApplicationSave(ApplicationData applicationData) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        try {
            connection = xuguUtils.getConnection();

            if (applicationData.getId() == null) {
                // 新增操作
                StringBuilder sqlUser = new StringBuilder("INSERT INTO APPLICATION_DATA (");
                StringBuilder placeholders = new StringBuilder(" VALUES (");
                List<Object> values = new ArrayList<>();

                // 使用反射获取属性值
                if (applicationData.getProcessStatus() != null) {
                    sqlUser.append("PROCESS_STATUS, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getProcessStatus());
                }
                if (applicationData.getApplicantUnit() != null) {
                    sqlUser.append("APPLICANT_UNIT, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getApplicantUnit());
                }
                if (applicationData.getApplicantDepartment() != null) {
                    sqlUser.append("APPLICANT_DEPARTMENT, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getApplicantDepartment());
                }
                if (applicationData.getApplicantUser() != null) {
                    sqlUser.append("APPLICANT_USER, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getApplicantUser());
                }
                if (applicationData.getContactInfo() != null) {
                    sqlUser.append("CONTACT_INFO, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getContactInfo());
                }
                if (applicationData.getPersonnelType() != null) {
                    sqlUser.append("PERSONNEL_TYPE, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getPersonnelType());
                }
                if (applicationData.getApplicationItem() != null) {
                    sqlUser.append("APPLICATION_ITEM, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getApplicationItem());
                }
                if (applicationData.getDataType() != null) {
                    sqlUser.append("DATA_TYPE, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getDataType());
                }
                if (applicationData.getApplyUser() != null) {
                    sqlUser.append("APPLY_USER, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getApplyUser());
                }
                if (applicationData.getSystemName() != null) {
                    sqlUser.append("SYSTEM_NAME, ");
                    placeholders.append("?, ");
                    values.add(applicationData.getSystemName());
                }

                if (values.isEmpty()) {
                    return result; // 如果没有任何字段，直接返回
                }

                sqlUser.setLength(sqlUser.length() - 2); // 去掉最后的逗号和空格
                placeholders.setLength(placeholders.length() - 2); // 去掉最后的逗号和空格

                sqlUser.append(")").append(placeholders).append(")");

                pstmtUser = connection.prepareStatement(sqlUser.toString());

                // 设置参数
                for (int i = 0; i < values.size(); i++) {
                    pstmtUser.setObject(i + 1, values.get(i));
                }

                result = pstmtUser.executeUpdate();  // 执行新增操作
            } else {
                // 修改操作
                StringBuilder sqlUser = new StringBuilder("UPDATE APPLICATION_DATA SET ");
                List<Object> values = new ArrayList<>();

                if (applicationData.getProcessStatus() != null) {
                    sqlUser.append("PROCESS_STATUS = ?, ");
                    values.add(applicationData.getProcessStatus());
                }
                if (applicationData.getApplicantUnit() != null) {
                    sqlUser.append("APPLICANT_UNIT = ?, ");
                    values.add(applicationData.getApplicantUnit());
                }
                if (applicationData.getApplicantDepartment() != null) {
                    sqlUser.append("APPLICANT_DEPARTMENT = ?, ");
                    values.add(applicationData.getApplicantDepartment());
                }
                if (applicationData.getApplicantUser() != null) {
                    sqlUser.append("APPLICANT_USER = ?, ");
                    values.add(applicationData.getApplicantUser());
                }
                if (applicationData.getContactInfo() != null) {
                    sqlUser.append("CONTACT_INFO = ?, ");
                    values.add(applicationData.getContactInfo());
                }
                if (applicationData.getPersonnelType() != null) {
                    sqlUser.append("PERSONNEL_TYPE = ?, ");
                    values.add(applicationData.getPersonnelType());
                }
                if (applicationData.getApplicationItem() != null) {
                    sqlUser.append("APPLICATION_ITEM = ?, ");
                    values.add(applicationData.getApplicationItem());
                }
                if (applicationData.getDataType() != null) {
                    sqlUser.append("DATA_TYPE = ?, ");
                    values.add(applicationData.getDataType());
                }
                if (applicationData.getApplyUser() != null) {
                    sqlUser.append("APPLY_USER = ?, ");
                    values.add(applicationData.getApplyUser());
                }
                if (applicationData.getSystemName() != null) {
                    sqlUser.append("SYSTEM_NAME = ?, ");
                    values.add(applicationData.getSystemName());
                }

                if (values.isEmpty()) {
                    return result; // 如果没有任何字段，直接返回
                }

                sqlUser.setLength(sqlUser.length() - 2); // 去掉最后的逗号和空格
                sqlUser.append(" WHERE ID = ?");  // 设置 WHERE 条件
                values.add(applicationData.getId());  // 添加 ID 条件

                pstmtUser = connection.prepareStatement(sqlUser.toString());

                // 设置参数
                for (int i = 0; i < values.size(); i++) {
                    pstmtUser.setObject(i + 1, values.get(i));
                }

                result = pstmtUser.executeUpdate();  // 执行更新操作
            }

            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 确保资源关闭
            if (pstmtUser != null) {
                try {
                    pstmtUser.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }


    @Override
    public int ApplicationUpdataById(ApplicationData applicationData) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        try {
            connection = xuguUtils.getConnection();
            // 修改操作
            StringBuilder sqlUser = new StringBuilder("UPDATE APPLICATION_DATA SET ");
            List<Object> values = new ArrayList<>();
            if (applicationData.getProcessStatus() != null) {
                sqlUser.append("PROCESS_STATUS = ?, ");
                values.add(applicationData.getProcessStatus());
            }
            if (applicationData.getApplicantUnit() != null) {
                sqlUser.append("APPLICANT_UNIT = ?, ");
                values.add(applicationData.getApplicantUnit());
            }
            if (applicationData.getApplicantDepartment() != null) {
                sqlUser.append("APPLICANT_DEPARTMENT = ?, ");
                values.add(applicationData.getApplicantDepartment());
            }
            if (applicationData.getApplicantUser() != null) {
                sqlUser.append("APPLICANT_USER = ?, ");
                values.add(applicationData.getApplicantUser());
            }
            if (applicationData.getContactInfo() != null) {
                sqlUser.append("CONTACT_INFO = ?, ");
                values.add(applicationData.getContactInfo());
            }
            if (applicationData.getPersonnelType() != null) {
                sqlUser.append("PERSONNEL_TYPE = ?, ");
                values.add(applicationData.getPersonnelType());
            }
            if (applicationData.getApplicationItem() != null) {
                sqlUser.append("APPLICATION_ITEM = ?, ");
                values.add(applicationData.getApplicationItem());
            }
            if (applicationData.getDataType() != null) {
                sqlUser.append("DATA_TYPE = ?, ");
                values.add(applicationData.getDataType());
            }
            if (applicationData.getApplyUser() != null) {
                sqlUser.append("APPLY_USER = ?, ");
                values.add(applicationData.getApplyUser());
            }
            if (applicationData.getSystemName() != null) {
                sqlUser.append("SYSTEM_NAME = ?, ");
                values.add(applicationData.getSystemName());
            }

            if (values.isEmpty()) {
                return result; // 如果没有任何字段，直接返回
            }

            sqlUser.setLength(sqlUser.length() - 2); // 去掉最后的逗号和空格
            sqlUser.append(" WHERE ID = ?");  // 设置 WHERE 条件
            values.add(applicationData.getId());  // 添加 ID 条件

            pstmtUser = connection.prepareStatement(sqlUser.toString());

            // 设置参数
            for (int i = 0; i < values.size(); i++) {
                pstmtUser.setObject(i + 1, values.get(i));
            }
            result = pstmtUser.executeUpdate();  // 执行更新操作
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 确保资源关闭
            if (pstmtUser != null) {
                try {
                    pstmtUser.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }

    @Override
    public int ApplicationDelById(ApplicationData applicationData) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        try {
            connection = xuguUtils.getConnection();
            // 根据 ID 删除记录的 SQL 语句
            String sql = "DELETE FROM APPLICATION_DATA WHERE ID = ?";
            pstmtUser = connection.prepareStatement(sql);
            // 设置参数：这里假设 applicationData.getId() 返回的是 ID 值
            pstmtUser.setString(1, applicationData.getId());  // 假设 ID 是 Long 类型，依据实际情况设置参数类型
            // 执行删除操作
            result = pstmtUser.executeUpdate();
            return result;  // 返回影响的行数，若删除成功，返回的值会大于 0
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 确保资源关闭
            if (pstmtUser != null) {
                try {
                    pstmtUser.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return 0;  // 如果发生异常或没有删除任何记录，返回 0
    }

    @Override
    public int VMApplicationSubmit(ApplicationData applicationData) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        try {
            connection = xuguUtils.getConnection();
            // 修改操作
            StringBuilder sqlUser = new StringBuilder("UPDATE APPLICATION_DATA SET ");
            List<Object> values = new ArrayList<>();
            if (applicationData.getDataType() != null) {
                sqlUser.append("DATA_TYPE = ?, ");
                values.add(applicationData.getDataType());
            }
            if (applicationData.getReviewer() != null) {
                sqlUser.append("REVIEWER = ?, ");
                values.add(applicationData.getReviewer());
            }
            if (values.isEmpty()) {
                return result; // 如果没有任何字段，直接返回
            }
            sqlUser.setLength(sqlUser.length() - 2); // 去掉最后的逗号和空格
            sqlUser.append(" WHERE ID = ?");  // 设置 WHERE 条件
            values.add(applicationData.getId());  // 添加 ID 条件
            pstmtUser = connection.prepareStatement(sqlUser.toString());
            // 设置参数
            for (int i = 0; i < values.size(); i++) {
                pstmtUser.setObject(i + 1, values.get(i));
            }
            result = pstmtUser.executeUpdate();  // 执行更新操作
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 确保资源关闭
            if (pstmtUser != null) {
                try {
                    pstmtUser.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }

    @Override
    public int VMApplicationWithdraw(ApplicationData applicationData) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        try {
            connection = xuguUtils.getConnection();
            // 修改操作
            StringBuilder sqlUser = new StringBuilder("UPDATE APPLICATION_DATA SET ");
            List<Object> values = new ArrayList<>();
            if (applicationData.getDataType() != null) {
                sqlUser.append("DATA_TYPE = ?, ");
                values.add(applicationData.getDataType());
            }
            if (applicationData.getReviewer() != null) {
                sqlUser.append("REVIEWER = ?, ");
                values.add(applicationData.getReviewer());
            }
            if (values.isEmpty()) {
                return result; // 如果没有任何字段，直接返回
            }
            sqlUser.setLength(sqlUser.length() - 2); // 去掉最后的逗号和空格
            sqlUser.append(" WHERE ID = ?");  // 设置 WHERE 条件
            values.add(applicationData.getId());  // 添加 ID 条件
            pstmtUser = connection.prepareStatement(sqlUser.toString());
            // 设置参数
            for (int i = 0; i < values.size(); i++) {
                pstmtUser.setObject(i + 1, values.get(i));
            }
            result = pstmtUser.executeUpdate();  // 执行更新操作
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 确保资源关闭
            if (pstmtUser != null) {
                try {
                    pstmtUser.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }
}
